﻿


CREATE PROCEDURE [dbo].[CreazaArhiva]
	@An smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @strContAn varchar(40)
	DECLARE @Comanda varchar(3000)
	DECLARE @existTableCont bit
	
	SET @strContAn = 'arhConturi'
	SET @existTableCont = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strContAn)
		SET @existTableCont = 1
	
	--verifica existenta tabeleli arhiva
	SET @Comanda = 'CREATE TABLE [' + @strContAn + '] (
		[Cont] [dbo].[TipCont] NOT NULL DEFAULT (''''),
		[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
		[Denumire] [varchar](200) NOT NULL DEFAULT (''''),
		[Tip] [char](1) NOT NULL DEFAULT (''A''),
		[Terminal] [bit] NOT NULL DEFAULT ((1)),
		[ContParinte] [dbo].[TipCont] NOT NULL DEFAULT (''''),
		[EsteAnalitic] [bit] NOT NULL DEFAULT ((1)),
		[DebitInitial] [decimal](20, 2) NOT NULL DEFAULT (0),
		[CreditInitial] [decimal](20, 2) NOT NULL DEFAULT (0),
		[DebitPrecedent] [decimal](20, 2) NOT NULL DEFAULT (0),
		[CreditPrecedent] [decimal](20, 2) NOT NULL DEFAULT (0),
		[Categorie] [tinyint] NOT NULL DEFAULT (0),
		[RulajDebit] [decimal](20, 2) NOT NULL DEFAULT (0),
		[RulajCredit] [decimal](20, 2) NOT NULL DEFAULT (0),
		[Sold] [decimal](20, 2) NOT NULL DEFAULT ((0)),
		[SortValue] [dbo].[TipCont] NOT NULL DEFAULT (''''),
		[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strContAn +'] PRIMARY KEY CLUSTERED([Cont] ASC,[CodUnitate] ASC,[An] ASC),
	CONSTRAINT [FK_'+@strContAn +'_' + @strContAn + '] FOREIGN KEY([ContParinte], [CodUnitate],[An]) REFERENCES ['+@strContAn+'] ([Cont], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strContAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate]),
	CONSTRAINT [CK_'+@strContAn+'_Tip] CHECK  (([Tip]=''A'' OR [Tip]=''P'' OR [Tip]=''B''))
	)'
	
	IF @existTableCont = 0
		EXEC(@Comanda)


	DECLARE @strAAAn varchar(40)
	DECLARE @existTableAA bit
	
	SET @strAAAn = 'arhArticoleAliniate' 
	SET @existTableAA = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strAAAn)
		SET @existTableAA = 1

	SET @Comanda = 'CREATE TABLE ['+@strAAAn+'](
	[ArtAl] [varchar](40) NOT NULL,
	[Denumire] [varchar](200) NOT NULL,
	[AAParinte] [varchar](40) NOT NULL,
	[Terminal] [bit] NOT NULL,
	[Titlu] [varchar](100) NOT NULL,
	[Categorii] [varchar](50) NOT NULL,
	[TermDenumire] [varchar](100) NOT NULL,
	[StartDenumire] [varchar](100) NOT NULL,
	[Anexa6] [bit] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[ExecutieCheltuieli] [bit] NOT NULL DEFAULT(0),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strAAAn+'] PRIMARY KEY CLUSTERED([ArtAl] ASC,[CodUnitate] ASC,[An] ASC),
	CONSTRAINT [FK_'+@strAAAn+'_'+@strAAAn+'] FOREIGN KEY([AAParinte], [CodUnitate],[An]) REFERENCES ['+@strAAAn+'] ([ArtAl], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strAAAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
	)'
	IF @existTableAA = 0
		EXEC(@Comanda)

	---Bilant----------------------------
	DECLARE @strBilantAn varchar(40)
	DECLARE @existTableBilant bit
	
	SET @strBilantAn = 'arhBilant'
	SET @existTableBilant = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strBilantAn)
		SET @existTableBilant = 1

	SET @Comanda = 'CREATE TABLE ['+@strBilantAn+'](
	[row] [int] NOT NULL,
	[nr_rand] [text] NOT NULL,
	[text] [text] NOT NULL,
	[codfix] [int] NOT NULL,
	[cod] [varchar](10) NOT NULL,
	[coltypei] [char](1) NOT NULL,
	[soldi] [text] NOT NULL,
	[coltypef] [char](1) NOT NULL,
	[soldf] [text] NOT NULL,
	[eval] [int] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[Boldat] [bit] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strBilantAn+'] PRIMARY KEY CLUSTERED([row] ASC,[An] ASC),
	CONSTRAINT [FK_'+@strBilantAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
	)'
	IF @existTableBilant = 0
		EXEC(@Comanda)
	---Conturi Valuta--------------------
	
	DECLARE @strContValutaAn varchar(40)
	DECLARE @existTableContValuta bit
	
	SET @strContValutaAn = 'arhConturiValuta'
	SET @existTableContValuta = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strContValutaAn)
		SET @existTableContValuta = 1

	SET @Comanda = 'CREATE TABLE ['+@strContValutaAn+'](
	[CodValuta] [smallint] NOT NULL DEFAULT ((0)),
	[Cont] [dbo].[TipCont] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[DebitInitial] [decimal](20, 2) NOT NULL DEFAULT ((0)),
	[CreditInitial] [decimal](20, 2) NOT NULL DEFAULT ((0)),
	[DebitPrecedent] [decimal](20, 2) NOT NULL DEFAULT ((0)),
	[CreditPrecedent] [decimal](20, 2) NOT NULL DEFAULT ((0)),
	[IdJurnal] [int] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strContValutaAn+'] PRIMARY KEY CLUSTERED([CodValuta] ASC,[Cont] ASC,[CodUnitate] ASC,[IdJurnal] ASC,[An] ASC),
	CONSTRAINT [FK_'+@strContValutaAn+'_'+@strContAn+'] FOREIGN KEY([Cont], [CodUnitate],[An]) REFERENCES ['+@strContAn+'] ([Cont], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strContValutaAn+'_Jurnale] FOREIGN KEY([IdJurnal]) REFERENCES [dbo].[Jurnale] ([IDJurnal]),
	CONSTRAINT [FK_'+@strContValutaAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate]),
	CONSTRAINT [FK_'+@strContValutaAn+'_Valuta] FOREIGN KEY([CodValuta]) REFERENCES [dbo].[Valuta] ([CodValuta])
	)'

	IF @existTableContValuta = 0
		EXEC(@Comanda)

	---Jurnale Solduri Initiale--------------------
	
	DECLARE @strJurnaleSIAn varchar(40)
	DECLARE @existTableJurnaleSI bit
	
	SET @strJurnaleSIAn = 'arhJurnaleSolduriInitiale'
	SET @existTableJurnaleSI = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strJurnaleSIAn)
		SET @existTableJurnaleSI = 1

	SET @Comanda = 'CREATE TABLE ['+@strJurnaleSIAn+'](
	[IDJurnal] [int] NOT NULL,
	[Cont] [dbo].[TipCont] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[DebitInitial] [decimal](18, 2) NOT NULL DEFAULT ((0)),
	[CreditInital] [decimal](18, 2) NOT NULL DEFAULT ((0)),
	[DebitPrecedent] [decimal](20, 2) NOT NULL DEFAULT ((0)),
	[CreditPrecedent] [decimal](20, 2) NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strJurnaleSIAn+'] PRIMARY KEY CLUSTERED ([IDJurnal] ASC,[Cont] ASC,[CodUnitate] ASC,[An] ASC),
	CONSTRAINT [FK_'+@strJurnaleSIAn+'_' + @strContAn + '] FOREIGN KEY([Cont], [CodUnitate],[An]) REFERENCES [' + @strContAn + '] ([Cont], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strJurnaleSIAn+'_Jurnale] FOREIGN KEY([IDJurnal]) REFERENCES [Jurnale] ([IDJurnal]),
	CONSTRAINT [FK_'+@strJurnaleSIAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [Unitati] ([CodUnitate])
	)'
	
	IF @existTableJurnaleSI = 0
		EXEC(@Comanda)


	---Ordonantari--------------------
	
	DECLARE @strOrdonantariAn varchar(40)
	DECLARE @existTableOrdonantari bit
	
	SET @strOrdonantariAn = 'arhOrdonantari'
	SET @existTableOrdonantari = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strOrdonantariAn)
		SET @existTableOrdonantari = 1

	SET @Comanda = 'CREATE TABLE ['+@strOrdonantariAn+'](
	[NrDoc] [decimal](18, 0) NOT NULL,
	[DataDoc] [datetime] NOT NULL,
	[IDFurnizor] [bigint] NOT NULL,
	[ValoareLei] [decimal](20, 2) NOT NULL,
	[IDJurnal] [int] NOT NULL,
	[Scop] [varchar](100) NOT NULL,
	[DocJust] [varchar](200) NOT NULL,
	[AngLegal] [varchar](50) NOT NULL,
	[Achitat] [bit] NOT NULL,
	[IdOrdonantare] [int] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT 0,
	[IdModPlata] [int] NOT NULL DEFAULT 1,
	[TotalDatorat] [decimal](18, 2) NOT NULL DEFAULT 0,
	[TotalAvansuri] [decimal](18, 2) NOT NULL DEFAULT 0,
	[ArtAl] [varchar](40) NOT NULL DEFAULT '''',
	[VizatCFP] [bit] NOT NULL,
	[MotivRefuz] [varchar](250) NOT NULL DEFAULT '''',
	[IDResponsabil] [int] NOT NULL DEFAULT 0,
	[DataVizaCFP] [datetime] NOT NULL DEFAULT (getdate()),
	[NrRegCFP] [varchar](20) NOT NULL DEFAULT (''FARA''),
	[IBANSursa] [varchar](24) NULL,
	[IBANDest] [varchar](24) NULL,
	[IDDocumentPlata] [int] NULL,
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strOrdonantariAn+'] PRIMARY KEY CLUSTERED ([IdOrdonantare] ASC),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_'+@strAAAn+'] FOREIGN KEY([ArtAl], [CodUnitate],[An]) REFERENCES ['+@strAAAn+'] ([ArtAl], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_Parteneri] FOREIGN KEY([IDfurnizor]) REFERENCES [dbo].[Parteneri] ([IdPartener]),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_Jurnale] FOREIGN KEY([IDJurnal]) REFERENCES [dbo].[Jurnale] ([IDJurnal]),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_ModPlata] FOREIGN KEY([IdModPlata]) REFERENCES [dbo].[ModPlata] ([IDModPlata]),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])	
	)'
	IF @existTableOrdonantari = 0
		EXEC(@Comanda)

	
	---RezultatPatrimonial--------------------
	
	DECLARE @strRezultatPatrimonialAn varchar(40)
	DECLARE @existTableRezultatPatrimonial bit
	
	SET @strRezultatPatrimonialAn = 'arhRezultatPatrimonial'
	SET @existTableRezultatPatrimonial = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strRezultatPatrimonialAn)
		SET @existTableRezultatPatrimonial = 1

	SET @Comanda = 'CREATE TABLE ['+@strRezultatPatrimonialAn+'](
	[row] [int] NOT NULL,
	[nr_rand] [text] NOT NULL,
	[text] [text] NOT NULL,
	[codfix] [int] NOT NULL,
	[cod] [varchar](10) NOT NULL,
	[coltypei] [char](1) NOT NULL,
	[soldi] [text] NOT NULL,
	[coltypef] [char](1) NOT NULL,
	[soldf] [text] NOT NULL,
	[eval] [int] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[Boldat] [bit] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strRezultatPatrimonialAn+'] PRIMARY KEY CLUSTERED ([row] ASC,[CodUnitate] ASC,[An] ASC),
	CONSTRAINT [FK_'+@strRezultatPatrimonialAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
	)'
	IF @existTableRezultatPatrimonial = 0
		EXEC(@Comanda)

	---RulajeDoc--------------------
	
	DECLARE @strRulajeDocAn varchar(40)
	DECLARE @existTableRulajeDoc bit
	
	SET @strRulajeDocAn = 'arhRulajeDoc'
	SET @existTableRulajeDoc = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strRulajeDocAn)
		SET @existTableRulajeDoc = 1

	SET @Comanda = 'CREATE TABLE ['+@strRulajeDocAn+'](
	[IDDocument] [bigint] NOT NULL,
	[ValoareLei] [decimal](20, 2) NOT NULL DEFAULT (0),
	[ValoareValuta] [decimal](20, 2) NOT NULL DEFAULT (0),
	[NumarDoc] [decimal](18, 0) NOT NULL DEFAULT (0),
	[DataDoc] [datetime] NOT NULL DEFAULT (getdate()),
	[NumarNota] [varchar](50) NOT NULL DEFAULT (''''),
	[TipFormula] [smallint] NOT NULL DEFAULT (0),
	[IDJurnal] [int] NULL DEFAULT (0),
	[Observatii] [varchar](200) NULL,
	[xUser] [varchar](50) NOT NULL  DEFAULT (suser_sname()),
	[xHost] [varchar](50) NOT NULL DEFAULT (host_name()),
	[xDate] [datetime] NOT NULL  DEFAULT (0),
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strRulajeDocAn+'] PRIMARY KEY CLUSTERED ([IDDocument] ASC),
	CONSTRAINT [FK_'+@strRulajeDocAn+'_Jurnale] FOREIGN KEY([IDJurnal]) REFERENCES [Jurnale] ([IDJurnal]),
	CONSTRAINT [FK_'+@strRulajeDocAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [Unitati] ([CodUnitate]),
	CONSTRAINT [CK_'+@strRulajeDocAn+'_Tip_FORMULA] CHECK  (([TipFormula] = 3 or ([TipFormula] = 2 or ([TipFormula] = 1 or [TipFormula] = 0))))
	)'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)

	---Rulaje--------------------
	
	DECLARE @strRulajeAn varchar(40)
	DECLARE @existTableRulaje bit
	
	SET @strRulajeAn = 'arhRulaje'
	SET @existTableRulaje = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strRulajeAn)
		SET @existTableRulaje = 1

	SET @Comanda = 'CREATE TABLE ['+@strRulajeAn+'](
	[CodFormula] [bigint] NOT NULL,
	[ContDebit] [dbo].[TipCont] NOT NULL,
	[ContCredit] [dbo].[TipCont] NOT NULL,
	[CodValuta] [smallint] NOT NULL DEFAULT (0),
	[IDDocument] [bigint] NOT NULL,
	[ValoareValuta] [decimal](20, 2) NOT NULL DEFAULT (0),
	[Curs] [decimal](18, 4) NOT NULL DEFAULT (1),
	[ValoareLei] [decimal](20, 2) NOT NULL DEFAULT (0),
	[DataDocJust] [datetime] NOT NULL,
	[TipDocJust] [varchar](10) NOT NULL  DEFAULT (''NC''),
	[NumarDocJust] [decimal](18, 0) NOT NULL,
	[ExplicatiiDocJust] [varchar](200) NOT NULL,
	[NrCrt] [smallint] NOT NULL DEFAULT (1),
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strRulajeAn+'] PRIMARY KEY CLUSTERED ([CodFormula] ASC),
	CONSTRAINT [FK_'+@strRulajeAn+'_'+@strContAn+'_Credit] FOREIGN KEY([ContCredit], [CodUnitate],[An]) REFERENCES ['+@strContAn+'] ([Cont], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strRulajeAn+'_'+@strContAn+'_Debit] FOREIGN KEY([ContDebit], [CodUnitate],[An]) REFERENCES ['+@strContAn+'] ([Cont], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strRulajeAn+'_'+@strRulajeDocAn+'] FOREIGN KEY([IDDocument]) REFERENCES ['+@strRulajeDocAn+'] ([IDDocument]),
	CONSTRAINT [FK_'+@strRulajeAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate]),
	CONSTRAINT [FK_'+@strRulajeAn+'_Valuta] FOREIGN KEY([CodValuta]) REFERENCES [dbo].[Valuta] ([CodValuta])
	)'

	IF @existTableRulaje = 0
		EXEC(@Comanda)
	

	---VenituriSiCheltuieli--------------------
	
	DECLARE @strVenituriSiCheltuieliAn varchar(40)
	DECLARE @existTableVenituriSiCheltuieli bit
	
	SET @strVenituriSiCheltuieliAn = 'arhVenituriSiCheltuieli'
	SET @existTableVenituriSiCheltuieli = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strVenituriSiCheltuieliAn)
		SET @existTableVenituriSiCheltuieli = 1

	SET @Comanda = 'CREATE TABLE ['+@strVenituriSiCheltuieliAn+'](
	[row] [int] NOT NULL,
	[nr_rand] [text] NOT NULL,
	[text] [text] NOT NULL,
	[codfix] [int] NOT NULL,
	[cod] [varchar](20) NOT NULL,
	[coltypei] [char](1) NOT NULL,
	[soldi] [text] NOT NULL,
	[coltypef] [char](1) NOT NULL,
	[soldf] [text] NOT NULL,
	[eval] [int] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[Boldat] [bit] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strVenituriSiCheltuieliAn+'] PRIMARY KEY CLUSTERED ([row] ASC,[CodUnitate] ASC,[An] ASC)
	)'
	IF @existTableVenituriSiCheltuieli = 0
		EXEC(@Comanda)



END